﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace QuanLiNhaSachDAO
{
    public class BaoCaoDAO
    {
        public static DataTable layPhieuNhap(string tuNgay, string denNgay)
        {
            string sql = "";
            sql += "SELECT PHIEUNHAP.id AS [MaPhieuNhap], ";
		    sql += "PHIEUNHAP.MaNhanVien AS [MaNhanVien], ";
            sql += "NHANVIEN.Ten AS [TenNhanVien], ";
		    sql += "PHIEUNHAP.Ngay AS [ThoiGian], ";
		    sql += "PHIEUNHAP.TongTien AS [TongTien] ";
            sql += "FROM PHIEUNHAP INNER JOIN NHANVIEN ON PHIEUNHAP.MaNhanVien = NHANVIEN.id ";
            sql += "WHERE PHIEUNHAP.Ngay >= '" + tuNgay;
            sql += "' AND PHIEUNHAP.Ngay <= '" + denNgay + "'";
            SqlDataAdapter da = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        public static DataTable layPhieuXuat(string tuNgay, string denNgay)
        {
            string sql = "";
            sql += "SELECT HOADON.id AS [MaHoaDon], ";
            sql += "HOADON.MaNhanVien AS [MaNhanVien], ";
            sql += "NHANVIEN.Ten AS [TenNhanVien], ";
            sql += "HOADON.Ngay AS [ThoiGian], ";
            sql += "HOADON.TongTien AS [TongTien] ";
            sql += "FROM HOADON INNER JOIN NHANVIEN ON HOADON.MaNhanVien = NHANVIEN.id ";
            sql += "WHERE HOADON.Ngay >= '" + tuNgay;
            sql += "' AND HOADON.Ngay <= '" + denNgay + "'";
            SqlDataAdapter da = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        public static DataTable layChiTietPhieuNhap(string MaPhieuNhap)
        {
            string sql = "";
            sql += "SELECT CHITIETPHIEUNHAP.id AS [ID], ";
		    sql += "CHITIETPHIEUNHAP.MaPhieuNhap AS [Mã phiếu nhập], ";
		    sql += "CHITIETPHIEUNHAP.MaSach AS [Mã sách], ";
		    sql += "SACH.Ten AS [Tên sách], ";
		    sql += "SACH.GiaMua AS [Giá mua/cuốn], ";
            sql += "CHITIETPHIEUNHAP.SoLuong AS [Số lượng], ";
            sql += "NHACUNGCAP.Ten AS [Nhà cung cấp] ";
            sql += "FROM CHITIETPHIEUNHAP ";
		    sql += "INNER JOIN PHIEUNHAP ON CHITIETPHIEUNHAP.MaPhieuNhap = PHIEUNHAP.id ";
		    sql += "INNER JOIN SACH ON CHITIETPHIEUNHAP.MaSach = SACH.id ";
            sql += "INNER JOIN NHACUNGCAP ON SACH.MaNhaCungCap = NHACUNGCAP.id ";
            sql += "WHERE CHITIETPHIEUNHAP.MaPhieuNhap = " + MaPhieuNhap;
            SqlDataAdapter da = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            dt.Columns.Add("Tác Giả");
            dt.Columns.Add("Thể Loại");
            dt.Columns.Add("Nhà xuất bản");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                dt.Rows[i]["Tác Giả"] = TacGiaDAO.TimTacGia(dt.Rows[i][2].ToString());
                dt.Rows[i]["Thể Loại"] = TheLoaiDAO.TimTheLoai(dt.Rows[i][2].ToString());
                dt.Rows[i]["Nhà xuất bản"] = NhaXuatBanDAO.TimNXB(dt.Rows[i][2].ToString());
            }
            return dt;
        }

        public static DataTable layChiTietPhieuXuat(string MaHoaDon)
        {
            string sql = "";
            sql += "SELECT CHITIETHOADON.id AS [ID], ";
            sql += "CHITIETHOADON.MaHoaDon AS [Mã hóa đơn], ";
            sql += "CHITIETHOADON.MaSach AS [Mã sách], ";
            sql += "SACH.Ten AS [Tên sách], ";
            sql += "SACH.GiaBan AS [Giá bán/cuốn], ";
            sql += "CHITIETHOADON.SoLuong AS [Số lượng], ";
            sql += "NHACUNGCAP.Ten AS [Nhà cung cấp] ";
            sql += "FROM CHITIETHOADON ";
            sql += "INNER JOIN HOADON ON CHITIETHOADON.MaHOADON = HOADON.id ";
            sql += "INNER JOIN SACH ON CHITIETHOADON.MaSach = SACH.id ";
            sql += "INNER JOIN NHACUNGCAP ON SACH.MaNhaCungCap = NHACUNGCAP.id ";
            sql += "WHERE CHITIETHOADON.MaHoaDon = " + MaHoaDon;
            SqlDataAdapter da = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            dt.Columns.Add("Tác Giả");
            dt.Columns.Add("Thể Loại");
            dt.Columns.Add("Nhà xuất bản");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                dt.Rows[i]["Tác Giả"] = TacGiaDAO.TimTacGia(dt.Rows[i][2].ToString());
                dt.Rows[i]["Thể Loại"] = TheLoaiDAO.TimTheLoai(dt.Rows[i][2].ToString());
                dt.Rows[i]["Nhà xuất bản"] = NhaXuatBanDAO.TimNXB(dt.Rows[i][2].ToString());
            }
            return dt;
        }

        public static string layTongTienNhap(string tuNgay, string denNgay)
        {
            string sql = "";
            sql += "SELECT Sum(PHIEUNHAP.TongTien) ";
            sql += "FROM PHIEUNHAP ";
            sql += "WHERE PHIEUNHAP.Ngay >= '" + tuNgay + "'";
            sql += " AND PHIEUNHAP.Ngay <= '" + denNgay + "'";
            SqlDataAdapter da = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt.Rows[0][0].ToString();
        }
        public static string layTongTienXuat(string tuNgay, string denNgay)
        {
            string sql = "";
            sql += "SELECT Sum(HOADON.TongTien) ";
            sql += "FROM HOADON ";
            sql += "WHERE HOADON.Ngay >= '" + tuNgay + "'";
            sql += " AND HOADON.Ngay <= '" + denNgay + "'";
            SqlDataAdapter da = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt.Rows[0][0].ToString();
        }

        public static DataTable baoCaoSach(string tuNgay, string denNgay)
        {
            string sql = "";
            sql += "SELECT MaSach, Ten, DaBan, GiaBan, TongTienBan, DaNhap, GiaMua, TongTienMua, SoLuongTon FROM ";
            sql += "(SELECT DISTINCT SACH.id AS [MaSach], SACH.Ten, SUM(CHITIETHOADON.SoLuong) AS [DaBan], SACH.GiaBan AS [GiaBan], SUM(CHITIETHOADON.SoLuong)*SACH.GiaBan AS [TongTienBan] ";
            sql += "FROM SACH FULL JOIN CHITIETHOADON ON SACH.id = CHITIETHOADON.MaSach ";
            sql += "INNER JOIN HOADON ON CHITIETHOADON.MaHoaDon = HOADON.id ";
            sql += "WHERE HOADON.Ngay >= '" + tuNgay;
            sql += "' AND HOADON.Ngay <= '" + denNgay + "' ";
            sql += "GROUP BY SACH.id, SACH.Ten,SACH.GiaBan) AS tmpTable1 ";
	        sql += "FULL JOIN (";
	        sql += "SELECT DISTINCT SACH.id, SUM(CHITIETPHIEUNHAP.SoLuong) AS [DaNhap], SACH.GiaMua AS [GiaMua], SUM(CHITIETPHIEUNHAP.SoLuong)*SACH.GiaMua AS [TongTienMua] ";
	        sql += "FROM SACH FULL JOIN CHITIETPHIEUNHAP ON SACH.id = CHITIETPHIEUNHAP.MaSach ";
            sql += "INNER JOIN PHIEUNHAP ON CHITIETPHIEUNHAP.MaPhieuNhap = PHIEUNHAP.id ";
            sql += "WHERE PHIEUNHAP.Ngay >= '" + tuNgay;
            sql += "' AND PHIEUNHAP.Ngay <= '" + denNgay + "' ";
	        sql += "GROUP BY SACH.id, SACH.Ten, SACH.GiaMua) AS tmpTable ON tmpTable.id = MaSach ";
	        sql += "INNER JOIN (";
	        sql += "SELECT DISTINCT SACH.id, SUM(SACH.SoLuong)  AS [SoLuongTon] ";
	        sql += "FROM SACH ";
	        sql += "GROUP BY SACH.id ";
            sql += ") AS tmpTable2 ON tmpTable2.id = MaSach ";
            SqlDataAdapter da = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

    }
}
